﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ModelLayer;
using System.Data.SqlClient;

namespace Database
{
    public class NodesDB
    {
		private Connection connection;

		public NodesDB()
		{
            connection = Connection.getInstance();
		}

		public List<Node> getNodes()
		{
			return where("");
		}

		public Node getNodeById(int id)
		{
			return singleWhere("id = " + id);
		}

		private Node singleWhere(String wClause)
		{
			Node node = null;

			string query = "SELECT * FROM Nodes WHERE " + wClause;
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			if (reader.Read())
			{
                node = new Node();
                node.Id = reader.GetInt32(0);
                node.Title = reader.GetString(1);
                Coordinates coord = new Coordinates();
                coord.Latitude = (float)reader.GetDouble(2);
                coord.Longitude = (float)reader.GetDouble(3);
                node.Coordinates = coord;
                node.Type = (ModelLayer.Type)reader.GetInt32(4);
                node.Date = reader.GetDateTime(5);
			}

			reader.Close();
			connection.Close();

			return node;
		}

		private List<Node> where(String wClause)
		{
			List<Node> nodes = new List<Node>();
			string query;
			if (!wClause.Equals(""))
				query = "SELECT * FROM Nodes WHERE " + wClause;
			else
				query = "SELECT * FROM Nodes";
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			while (reader.Read())
			{
                Node node = new Node();
                node.Id = reader.GetInt32(0);
                node.Title = reader.GetString(1);
                Coordinates coord = new Coordinates();
                coord.Latitude = (float)reader.GetDouble(2);
                coord.Longitude = (float)reader.GetDouble(3);
                node.Coordinates = coord;
                node.Type = (ModelLayer.Type)reader.GetInt32(4);
                node.Date = reader.GetDateTime(5);
                nodes.Add(node);
			}

			reader.Close();
			connection.Close();

			return nodes;
		}

        public int insertNode(Node node)
        {
            int id = connection.GetMaxId("Nodes") + 1;
            string query = @"INSERT INTO Nodes (id, title, latitude, " + 
                "longitude, type, date) " +
                "VALUES ( " + id + ", '" + node.Title + "', " + 
                node.Coordinates.Latitude + ", " + node.Coordinates.Longitude
                 + ", " + (int)node.Type + ", '" +  node.Date + "')";
            connection.Open();
            int result = connection.Execute(query);
            connection.Close();

            if (result == 1)
                return id;
            return -1;
        }

        public bool updateNode(Node node)
        {
            string query = @"UPDATE Nodes SET title='" + node.Title +
                "', latitude=" + node.Coordinates.Latitude + ", longitude="
                + node.Coordinates.Longitude + ", type=" + (int)node.Type
                 + ", date='" + node.Date + "' WHERE id=" + node.Id;
            connection.Open();
            int result = connection.Execute(query);
            connection.Close();
            return result == 1;
        }

        public bool removeNode(int id)
        {
            string query = "DELETE FROM Nodes WHERE id=" + id;
            connection.Open();
            int result = connection.Execute(query);
            connection.Close();
            return result == 1;
        }
    }
}
